﻿Imports System.Data.SqlClient
Imports System.Data

Public Class AcessoDados
    Private conexao As SqlConnection
    Public StringConexao As String

    Public Sub New(ByVal strStringConexao As String)
        StringConexao = strStringConexao
    End Sub

    Public Sub New()

    End Sub

    Public Function AbreConexao() As Boolean
        'verifica se ja existe uma a conexao
        If conexao Is Nothing OrElse _
        conexao.State = ConnectionState.Closed OrElse _
        conexao.State = ConnectionState.Broken Then
            conexao = New SqlConnection()
            conexao.ConnectionString = StringConexao
            conexao.Open()
            Return True
        Else
            Throw New Exception("Erro: Conexao ja esta aberta.")
            Return False
        End If
    End Function

    Public Sub FechaConexao()
        If (conexao IsNot Nothing AndAlso conexao.State <> ConnectionState.Closed) Then
            conexao.Close()
            conexao.Dispose()
            conexao = Nothing
        End If
    End Sub
    Public Function ExecutaSql(ByVal sql As String) As Integer
        Dim qte As Integer
        Dim comando As SqlCommand = New SqlCommand()
        AbreConexao()
        Try
            comando.CommandText = sql
            comando.CommandType = CommandType.Text
            comando.Connection = conexao
            qte = comando.ExecuteNonQuery()
        Catch ex As Exception
            Throw New Exception("Erro: " & ex.Message)
        Finally
            FechaConexao()
        End Try
        Return qte
    End Function

    Public Function ExecutaSqlRetorno(ByVal sql As String) As DataSet
        Dim Dados As DataSet = New DataSet()
        Dim comando As SqlCommand = New SqlCommand()
        Dim adaptador As SqlDataAdapter = New SqlDataAdapter()
        AbreConexao()
        Try
            comando.CommandText = sql
            comando.CommandType = CommandType.Text
            comando.Connection = conexao
            adaptador.SelectCommand = comando
            adaptador.Fill(Dados)
        Catch ex As Exception
            Throw New Exception("Erro: " & ex.Message)
        Finally
            FechaConexao()
        End Try
        Return Dados
    End Function

    Public Function ExecutaSqlRetornoDataTable(ByVal sql As String) As DataTable
        Dim Dados As DataTable = New DataTable()
        Dim comando As SqlCommand = New SqlCommand()
        Dim adaptador As SqlDataAdapter = New SqlDataAdapter()
        AbreConexao()
        Try
            comando.CommandText = sql
            comando.CommandType = CommandType.Text
            comando.Connection = conexao
            adaptador.SelectCommand = comando
            adaptador.Fill(Dados)
        Catch ex As Exception
            Throw New Exception("Erro: " & ex.Message)
        Finally
            FechaConexao()
        End Try
        Return Dados
    End Function
End Class